﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SqlClient;
using System.IO;

namespace blackhouse.Data {

    [Obsolete]
    public class SqlVersioning {

        #region Fields

        private string connectionString;
        private MsSqlAdoDataHelper dataHelper;
        private Assembly assembly;
        private Version dbVersion;
        private Version scriptLastVersion;
        private static Dictionary<string, Version> dbVersions = new Dictionary<string, Version>();
        private static Dictionary<string, Version> scriptLastVersions = new Dictionary<string, Version>();
        private string[] scriptResources;

        #endregion

        public SqlVersioning(string sqlConnection, Assembly versionedAssembly) {
            this.connectionString = sqlConnection;
            this.dataHelper = new MsSqlAdoDataHelper(sqlConnection);
            this.assembly = versionedAssembly;
            try {
                this.dbVersion = this.ReadDbVersion(versionedAssembly.GetName().Name);
            } catch (Exception) {
                this.InstallVersioning();
                this.dbVersion = this.ReadDbVersion(versionedAssembly.GetName().Name);
            }

            this.scriptLastVersion = this.ReadScriptLastVersion();
            if (this.dbVersion < this.scriptLastVersion)
                this.Update();
        }

        #region Methods

        private void Update() {
            string functionalityName = assembly.GetName().Name;
            foreach (string scriptFile in this.scriptResources) {
                Version sFileVersion = this.ParseVersionFromFileName(scriptFile);
                if (sFileVersion > this.dbVersion) {
                    string script = this.ReadScript(assembly, scriptFile);
                    if (!string.IsNullOrEmpty(script)) {
                        if (!this.RunScript(script, sFileVersion, functionalityName))
                            throw new InvalidOperationException("Wystąpił błąd przy instalacji " + functionalityName);
                        this.dbVersion = sFileVersion;
                        dbVersions[functionalityName] = this.dbVersion;
                    }
                }
            }
            // w pętli:
            // 1.odczytujemy wersję z pliku           2. Jeżeli wyższa niż DbVersion to wykonujemy plik
        }

        private Version ReadScriptLastVersion() {
            if (scriptLastVersions.ContainsKey(assembly.GetName().Name))
                return scriptLastVersions[assembly.GetName().Name];

            Version version = null;
            lock (scriptLastVersions) {

                this.ReadAndSetScriptFiles();
                if (scriptResources.Length == 0)
                    version = new Version(0, 0, 0, 0);
                else
                    version = this.ParseVersionFromFileName(scriptResources[scriptResources.Length - 1]);
                scriptLastVersions.Add(assembly.GetName().Name, version);
            }
            return version;
        }

        private Version ParseVersionFromFileName(string fileName) {
            string[] fileNamesParts = fileName.Split('.');
            return new Version(string.Join(".", fileNamesParts.Skip(fileNamesParts.Length - 5).Take(4).ToArray()));
        }

        private void ReadAndSetScriptFiles() {
            this.scriptResources = assembly.GetManifestResourceNames().Where(s => s.EndsWith(".sql")).OrderBy(s => s).ToArray();
        }

        private Version ReadDbVersion(string functionalityName) {
            if (dbVersions.ContainsKey(functionalityName))
                return dbVersions[functionalityName];

            Version version = null;
            lock (dbVersions) {
                this.dataHelper.Reset();
                this.dataHelper.ProcedureName = "bhp_VersionsGet";
                this.dataHelper.AddParameter("FunctionalityName", functionalityName);
                string sVersion = this.dataHelper.ExecuteScalar<string>();
                if (string.IsNullOrEmpty(sVersion))
                    version = new Version(0, 0, 0, 0);
                else
                    version = new Version(sVersion);
                dbVersions.Add(functionalityName, version);
            }
            return version;
        }

        private void InstallVersioning() {
            string installScript = this.ReadScript(this.GetType().Assembly, "blackhouse.Data.sqlversioning.sql");
            this.RunScript(installScript);
        }

        private string ReadScript(Assembly assembly, string file) {
            string sql = String.Empty;
            using (Stream st = assembly.GetManifestResourceStream(file)) {
                if (st != null) {
                    using (StreamReader sr = new StreamReader(st)) {
                        sql = sr.ReadToEnd();
                    }
                }
            }
            return sql;
        }

        private string[] PrepareSctipts(string script) {
            string[] delimiter = new string[] { "GO\r\n" };
            //script = script.Replace("GO", "GO\r\n");
            string[] arrSqlStatements = script.Split(delimiter, StringSplitOptions.RemoveEmptyEntries);
            return arrSqlStatements;
        }

        private bool RunScript(string script, Version version = null, string functionalityName = "") {
            if (string.IsNullOrEmpty(script)) return true;
            bool result = false;

            SqlConnection connection = new SqlConnection(this.connectionString);
            connection.Open();

            SqlTransaction transaction = connection.BeginTransaction(); 
            string currentStatement = string.Empty;

            try {
                foreach (String sqlStatement in this.PrepareSctipts(script)) {
                    if (sqlStatement.Trim().Length > 0) {
                        currentStatement = sqlStatement;
                        SqlCommand command = connection.CreateCommand();
                        command.Transaction = transaction;
                        command.CommandText = sqlStatement;
                        try {
                            command.ExecuteNonQuery();
                        }
                        catch (SqlException sqle) {
                            throw new Exception("Błąd aktualizacji w:<br />" + sqlStatement + "<br />" + sqle.Message, sqle);
                        }
                    }
                }

                if (version != null && !string.IsNullOrEmpty(functionalityName)) {
                    SqlCommand updateVersionCommand = connection.CreateCommand();
                    updateVersionCommand.Transaction = transaction;
                    updateVersionCommand.CommandType = System.Data.CommandType.StoredProcedure;
                    updateVersionCommand.CommandText = "bhp_VersionsSet";
                    updateVersionCommand.Parameters.AddWithValue("@FunctionalityName", functionalityName);
                    updateVersionCommand.Parameters.AddWithValue("@DbVersion", version.ToString());
                    updateVersionCommand.ExecuteNonQuery();
                }

                transaction.Commit();
                result = true;

            } catch (SqlException) {
                transaction.Rollback();
                if (dbVersions.ContainsKey(functionalityName))
                    dbVersions.Remove(functionalityName);
                if (scriptLastVersions.ContainsKey(functionalityName))
                    scriptLastVersions.Remove(functionalityName);
            } finally {
                connection.Close();

            }

            return result;
        }

        #endregion

    }

}
